-- 菜单
create table SYS_MENU
(
  MENU_ID     NUMBER  primary key,
  PARENT_ID   NUMBER ,
  NAME     VARCHAR2(70) not null,
  URL    VARCHAR2(100),
  PERMS  VARCHAR2(200),
  TYPE VARCHAR2(15),
  ICON  VARCHAR2(30),
  ORDER_NUM  NUMBER
);
-- Add comments to the table
comment on table SYS_MENU
  is '菜单管理';
-- Add comments to the columns
comment on column SYS_MENU.MENU_ID
  is '自增长主键';
comment on column SYS_MENU.PARENT_ID
  is '父菜单ID，一级菜单为0';
comment on column SYS_MENU.URL
  is '菜单URL';
comment on column SYS_MENU.NAME
  is '菜单名称';
comment on column SYS_MENU.PERMS
  is '授权(多个用逗号分隔，如：user:list,user:create)';
comment on column SYS_MENU.TYPE
  is '类型   0：目录   1：菜单   2：按钮';
comment on column SYS_MENU.ICON
  is '菜单图标';
comment on column SYS_MENU.ORDER_NUM
  is '排序';

DROP TRIGGER SY_MENU;
DROP SEQUENCE MENU_SEQ;

create sequence MENU_SEQ

increment by 1

start with 300

nomaxvalue

nominvalue

nocache


create or replace trigger SY_MENU

before insert on SYS_MENU

for each row

begin

select MENU_SEQ.nextval into :new.MENU_ID from dual;

end;

-- 系统用户
create table SYS_USER
(
  USER_ID     NUMBER  primary key,
  USERNAME   VARCHAR2(100),
  "PASSWORD"   VARCHAR2(100) not null,
  EMAIL      VARCHAR2(100),
  MOBILE     VARCHAR2(100),
  STATUS     NUMBER,
  CREATE_USER_ID  NUMBER,
  CREATE_TIME  TIMESTAMP(6) default SYSTIMESTAMP not null
);
-- Add comments to the table
comment on table SYS_USER
  is '系统用户';
-- Add comments to the columns
comment on column SYS_USER.USER_ID
  is '自增长主键';
comment on column SYS_USER.USERNAME
  is '用户名';
comment on column SYS_USER.PASSWORD
  is '密码';
comment on column SYS_USER.EMAIL
  is '邮箱';
comment on column SYS_USER.MOBILE
  is '手机号';
comment on column SYS_USER.STATUS
  is '状态  0：禁用   1：正常';
comment on column SYS_USER.CREATE_USER_ID
  is '创建者ID';
comment on column SYS_USER.CREATE_TIME
  is '创建时间';

create sequence USER_SEQ

increment by 1

start with 1

nomaxvalue

nominvalue

nocache

DROP TRIGGER SY_USER;

create or replace trigger SY_USER

before insert on SYS_USER

for each row

begin

select USER_SEQ.nextval into :new.USER_ID from dual;

end;

-- 角色
create table SYS_ROLE
(
  ROLE_ID     NUMBER  primary key,
  ROLE_NAME   VARCHAR2(100),
  REMARK   VARCHAR2(100) not null,
  CREATE_USER_ID  NUMBER,
  CREATE_TIME  TIMESTAMP(6) default SYSTIMESTAMP not null
);
-- Add comments to the table
comment on table SYS_ROLE
  is '角色';
-- Add comments to the columns
comment on column SYS_ROLE.ROLE_ID
  is '自增长主键';
comment on column SYS_ROLE.ROLE_NAME
  is '角色名称';
comment on column SYS_ROLE.REMARK
  is '备注';
comment on column SYS_ROLE.CREATE_USER_ID
  is '创建者ID';
comment on column SYS_ROLE.CREATE_TIME
  is '创建时间';

create sequence ROLE_SEQ

increment by 1

start with 1

nomaxvalue

nominvalue

nocache

DROP TRIGGER SY_ROLE;

create or replace trigger SY_ROLE

before insert on SYS_ROLE

for each row

begin

select ROLE_SEQ.nextval into :new.ROLE_ID from dual;

end;

-- 用户与角色对应关系
create table SYS_USER_ROLE
(
  ID     NUMBER  primary key,
  USER_ID   NUMBER,
  ROLE_ID   NUMBER
);


DROP TRIGGER SY_USER_ROLE;
create or replace trigger SY_USER_ROLE

before insert on SYS_USER_ROLE

for each row

begin

select USER_ROLE_SEQ.nextval into :new.ID from dual;

end;

-- Add comments to the table
comment on table SYS_USER_ROLE
  is '用户与角色对应关系';
-- Add comments to the columns
comment on column SYS_USER_ROLE.ID
  is '自增长主键';
comment on column SYS_USER_ROLE.USER_ID
  is '用户ID';
comment on column SYS_USER_ROLE.ROLE_ID
  is '角色ID';

create sequence USER_ROLE_SEQ

increment by 1

start with 1

nomaxvalue

nominvalue

nocache

-- 角色与菜单对应关系
create table SYS_ROLE_MENU
(
  ID     NUMBER  primary key,
  ROLE_ID   NUMBER,
  MENU_ID   NUMBER

);

DROP TRIGGER SY_ROLE_MENU;

create or replace trigger SY_ROLE_MENU

before insert on SYS_ROLE_MENU

for each row

begin

select ROLE_MENU_SEQ.nextval into :new.ID from dual;

end;


-- Add comments to the table
comment on table SYS_ROLE_MENU
  is '用户与角色对应关系';
-- Add comments to the columns
comment on column SYS_ROLE_MENU.ID
  is '自增长主键';
comment on column SYS_ROLE_MENU.MENU_ID
  is '菜单ID';
comment on column SYS_ROLE_MENU.ROLE_ID
  is '角色ID';


create sequence ROLE_MENU_SEQ

increment by 1

start with 1

nomaxvalue

nominvalue

nocache

create or replace trigger SY_ROLE_MENU

before insert on SYS_ROLE_MENU

for each row

begin

select ROLE_MENU_SEQ.nextval into :new.ID from dual;

end;

drop table SYS_CONFIG;
-- 系统配置信息
create table SYS_CONFIG
(
  ID     NUMBER  primary key,
  KEY   VARCHAR2(100),
  VALUE   VARCHAR2(4000) not null,
  STATUS  NUMBER,
  REMARK   VARCHAR2(100)
);

-- Add comments to the table
comment on table SYS_CONFIG
  is '角色';
-- Add comments to the columns
comment on column SYS_CONFIG.ID
  is '自增长主键';
comment on column SYS_CONFIG.KEY
  is 'KEY';
comment on column SYS_CONFIG.VALUE
  is 'VALUE';
comment on column SYS_CONFIG.STATUS
  is '状态   0：隐藏   1：显示';
comment on column SYS_CONFIG.REMARK
  is '备注';

create or replace trigger SY_CONFIG

before insert on SYS_CONFIG

for each row

begin

select CONFIG_SEQ.nextval into :new.ID from dual;

end;

create sequence CONFIG_SEQ

increment by 1

start with 1

nomaxvalue

nominvalue

nocache

-- 系统日志
create table SYS_LOG
(
  ID     NUMBER  primary key,
  USERNAME   VARCHAR2(100),
  OPERATION   VARCHAR2(100) not null,
  METHOD   VARCHAR2(200),
  PARAMS   VARCHAR2(4000),
  IP       VARCHAR2(200),
  CREATE_TIME  TIMESTAMP(6) default SYSTIMESTAMP not null
);

create sequence LOG_SEQ

increment by 1

start with 1

nomaxvalue

nominvalue

nocache


create or replace trigger SY_LOG

before insert on SYS_LOG

for each row

begin

select LOG_SEQ.nextval into :new.ID from dual;

end;

-- Add comments to the table
comment on table SYS_LOG
  is '系统日志';
-- Add comments to the columns
comment on column SYS_LOG.ID
  is '自增长主键';
comment on column SYS_LOG.USERNAME
  is '用户名';
comment on column SYS_LOG.OPERATION
  is '用户操作';
comment on column SYS_LOG.METHOD
  is '请求方法';
comment on column SYS_LOG.PARAMS
  is '请求参数';
comment on column SYS_LOG.IP
  is 'IP地址';
comment on column SYS_LOG.CREATE_TIME
  is '创建时间';

-- 文件上传
create table SYS_OSS
(
  ID     NUMBER  primary key,
  URL   VARCHAR2(400),
 CREATE_TIME  TIMESTAMP(6) default SYSTIMESTAMP not null
);


create or replace trigger SY_OSS

before insert on SYS_OSS

for each row

begin

select OSS_SEQ.nextval into :new.ID from dual;

end;


create sequence OSS_SEQ

increment by 1

start with 1

nomaxvalue

nominvalue

nocache

-- Add comments to the table
comment on table SYS_OSS
  is '文件上传';
-- Add comments to the columns
comment on column SYS_OSS.ID
  is '自增长主键';
comment on column SYS_OSS.URL
  is 'URL地址';
comment on column SYS_LOG.CREATE_TIME
  is '创建时间';




begin
-- 初始数据
INSERT INTO sys_menu (menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES ('1', '0', '系统管理', NULL, NULL, '0', 'fa fa-cog', '0');
INSERT INTO sys_menu (menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES ('2', '1', '管理员列表', 'sys/user.html', NULL, '1', 'fa fa-user', '1');
INSERT INTO sys_menu (menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES ('3', '1', '角色管理', 'sys/role.html', NULL, '1', 'fa fa-user-secret', '2');
INSERT INTO sys_menu (menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES ('4', '1', '菜单管理', 'sys/menu.html', NULL, '1', 'fa fa-th-list', '3');
INSERT INTO sys_menu (menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES ('5', '1', 'SQL监控', 'druid/sql.html', NULL, '1', 'fa fa-bug', '4');
INSERT INTO sys_menu (menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES ('15', '2', '查看', NULL, 'sys:user:list,sys:user:info', '2', NULL, '0');
INSERT INTO sys_menu (menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES ('16', '2', '新增', NULL, 'sys:user:save,sys:role:select', '2', NULL, '0');
INSERT INTO sys_menu (menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES ('17', '2', '修改', NULL, 'sys:user:update,sys:role:select', '2', NULL, '0');
INSERT INTO sys_menu (menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES ('18', '2', '删除', NULL, 'sys:user:delete', '2', NULL, '0');
INSERT INTO sys_menu (menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES ('19', '3', '查看', NULL, 'sys:role:list,sys:role:info', '2', NULL, '0');
INSERT INTO sys_menu (menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES ('20', '3', '新增', NULL, 'sys:role:save,sys:menu:perms', '2', NULL, '0');
INSERT INTO sys_menu (menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES ('21', '3', '修改', NULL, 'sys:role:update,sys:menu:perms', '2', NULL, '0');
INSERT INTO sys_menu (menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES ('22', '3', '删除', NULL, 'sys:role:delete', '2', NULL, '0');
INSERT INTO sys_menu (menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES ('23', '4', '查看', NULL, 'sys:menu:list,sys:menu:info', '2', NULL, '0');
INSERT INTO sys_menu (menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES ('24', '4', '新增', NULL, 'sys:menu:save,sys:menu:select', '2', NULL, '0');
INSERT INTO sys_menu (menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES ('25', '4', '修改', NULL, 'sys:menu:update,sys:menu:select', '2', NULL, '0');
INSERT INTO sys_menu (menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES ('26', '4', '删除', NULL, 'sys:menu:delete', '2', NULL, '0');
INSERT INTO sys_menu (menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES ('27', '1', '参数管理', 'sys/config.html', 'sys:config:list,sys:config:info,sys:config:save,sys:config:update,sys:config:delete', '1', 'fa fa-sun-o', '6');
INSERT INTO sys_menu (menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES ('29', '1', '系统日志', 'sys/log.html', 'sys:log:list', '1', 'fa fa-file-text-o', '7');
--INSERT INTO sys_menu (menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES ('30', '1', '文件上传', 'sys/oss.html', 'sys:oss:all', '1', 'fa fa-file-image-o', '6');


-- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 代码生成器相关SQL，如果不使用renren-gen模块，则不用执行下面SQL -------------------------------------------------------------------------------------------------------------
-- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

INSERT INTO sys_menu (menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES ('28', '1', '代码生成器', 'sys/generator.html', 'sys:generator:list,sys:generator:code', '1', 'fa fa-rocket', '8');
end;